package com.hs.pre.module.sort;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.hs.HsBase;
import com.hs.common.ExtTreeBean;
import com.hs.core.db.DBOperator;
import com.hs.core.db.DatabaseConnection;
import com.hs.core.db.HsPreparedStatement;
import com.hs.core.db.HsResultSet;
import com.hs.pre.model.SortBean;
import com.hs.pre.model.SortExcelBean;
import com.hs.pre.page.PageParam;
import com.hs.pre.page.PageQuery;
import com.hs.pre.page.PageUtil;
import com.hs.pre.page.ReverseLoadData;

public class SortDAO {

	/**
	 * 通过父id 查出子节点
	 * @param parentId
	 * @return
	 */
	public List  getSortTreeList(Integer parentId)
	{
		DBOperator dbo = null;
		try {
			dbo = DatabaseConnection.getDBOperator();
		} catch (Exception e) { 
		}
		if(dbo == null)
			return null;
		List  listData = new ArrayList(); 
		String sql = (String) HsBase.getAnsiSqlMap().get("pre_sort.select")+" order by sortId desc";
		String isleaf = (String) HsBase.getAnsiSqlMap().get("pre_sort.isleaf");
		
		List list02 = dbo.select(isleaf, new Object[]{parentId});
		Iterator iterator = list02.iterator();
		Set set = new HashSet();
		while(iterator.hasNext())
		{
			Map row = (Map)iterator.next();
			set.add(row.get("parentSortId"));
		}
		
		List list = dbo.select(sql, new Object[]{parentId});
		 iterator = list.iterator();
		while(iterator.hasNext())
		{
			Map row = (Map)iterator.next();
			ExtTreeBean extTreeBean = new ExtTreeBean();
			extTreeBean.setText(row.get("sortName").toString());
			extTreeBean.setId(row.get("sortId"));
			if(set.add(row.get("sortId")))
					extTreeBean.setLeaf(true);
			else extTreeBean.setLeaf(false);
			listData.add(extTreeBean);
		}
		
		return listData;
	}
	
	/**
	 * 删除分类
	 * @param sortId
	 * @return
	 */
	public boolean delSort(Integer sortId)
	{	
		DBOperator dbo = null;
		try {
			dbo = DatabaseConnection.getDBOperator();
	
		if (dbo == null)
			return false;
		String sql = (String) HsBase.getAnsiSqlMap().get("pre_sort.delete");
		dbo.update(sql, new Object[] { sortId });
		
		} catch (Exception e) {
			return false;
		}
		return true;
	}
	
	/**
	 * 更新分类
	 * @param sortId
	 * @return
	 */
	public boolean updateSort(Integer parentSortId,Integer sortId)
	{	
		DBOperator dbo = null;
		try {
			dbo = DatabaseConnection.getDBOperator();
			if (dbo == null)
				return false;
			String sql = (String) HsBase.getAnsiSqlMap().get("pre_sort.update-pid");
			dbo.update(sql, new Object[] {parentSortId,sortId});
		} catch (Exception e) {
			return false;
		}
		return true;
	}
	
	/***
	 * 添加分类
	 * @param sortBean
	 * @return
	 */
	public SortBean addSort(SortBean sortBean)
	{	
		DBOperator dbo = null;
		Connection connection =null; 
		try {
			dbo = DatabaseConnection.getDBOperator();
		if (dbo == null)
			return sortBean;
	
		String sql = (String) HsBase.getAnsiSqlMap().get("pre_sort.insert");
		connection = dbo.getConnection();
		PreparedStatement p = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		p.setString(1, sortBean.getSortName());
		p.setInt(2, sortBean.getParentSortId());
		p.execute();
		ResultSet keys = p.getGeneratedKeys(); 
		if (keys.next()) { 
			sortBean.setSortId(new Integer(keys.getInt(1))); 
		} 

		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				connection = null;
		}
		return sortBean;
	}
	
	/**
	 * 编辑分类
	 * @param sortBean
	 * @return
	 */
	public SortBean editSort(SortBean sortBean)
	{	
		DBOperator dbo = null;
		try {
			dbo = DatabaseConnection.getDBOperator();
		if (dbo == null)
			return sortBean;
		String sql = (String) HsBase.getAnsiSqlMap().get("pre_sort.update");;
		dbo.update(sql, new Object[]{sortBean.getSortName(),sortBean.getSortId()});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sortBean;
	}
	
	
	/**
	 * 获得最上级分类列表
	 * @return
	 */
	public List getSortSearch(){
		String sql = (String) HsBase.getAnsiSqlMap().get("pre_sort.sortsearch");
		return this.getSortBean(sql, null);
	}
	
	
	/**
	 *  获得分类bean
	 * @param sql
	 * @param par
	 * @return
	 */
	private List getSortBean(String sql,Object[] par)
	{
		   DBOperator dbo = null;
			try {
				dbo = DatabaseConnection.getDBOperator();
			} catch (Exception e) {
				e.printStackTrace();
			}
			if (dbo == null)
				return null;
		List listData = new ArrayList();
		List list = dbo.select(sql,par);
		Iterator iterator = list.iterator();
		while(iterator.hasNext())
		{
			Map row = (Map)iterator.next();
			SortBean sortBean = new SortBean();
			sortBean.setSortId(new Integer(((Long)row.get("sortId")).intValue()));
			sortBean.setSortName(row.get("sortName").toString());
			sortBean.setParentSortId(new Integer(((Long)row.get("parentSortId")).intValue()));
			listData.add(sortBean);
		}

		return listData;
	}
	
	/**
	 *  传入分类父节点id，
	 *  获得下面的所有子目录节点
	 * @param pid
	 * @return
	 */
	public List getSortCatalog(Long pid)
	{
		String sql = (String) HsBase.getAnsiSqlMap().get("pre_sort.sortcatalog");
		List result = new ArrayList();
		List list = this.getSortBean(sql, null);
		Map map = new HashMap();
		if(!list.isEmpty())
		{
			Iterator iterator = list.iterator();
			SortBean sortBean = null;
			while(iterator.hasNext())
			{
				SortBean sortBeanTem = (SortBean)iterator.next();
				if(sortBeanTem != null)
				{
					if(sortBeanTem.getSortId() == pid.intValue())
						sortBean = sortBeanTem;
					if(map.containsKey(sortBeanTem.getParentSortId()))
					{
						List listTem = (List)map.get(sortBeanTem.getParentSortId());
						listTem.add(sortBeanTem);
					}else
					{
						List listTem = new ArrayList();
						listTem.add(sortBeanTem);
						map.put(sortBeanTem.getParentSortId(),listTem);
					}
				}
			}
		
			if(sortBean != null)
				this.getTreeSort(result, sortBean, map);
		}
		return result;
	}
	
	
	/**
	 *  获得子分类目录
	 * @param list
	 * @param sortBeanTem
	 * @param map
	 * @return
	 */
	public void getTreeSort(List list,SortBean sortBean,Map map)
	{
		list.add(sortBean);
		if(map.containsKey(sortBean.getSortId()))
		{
			List  listTem = (List)map.get(sortBean.getSortId());
			Iterator  iterator = listTem.iterator();
			while(iterator.hasNext())
			{
				SortBean sortBeanTem = (SortBean)iterator.next();
				this.getTreeSort(list, sortBeanTem, map);
			}
			
		}
	}
	
	/**
	 *  获得 分类主键为parentId的分类的下级分类
	 * @param parentId 父分类id
	 * @param pageSize 查询个数
	 * @return
	 */
	public List getSortSearchChi(Integer parentId,Integer pageSize){
			String sql = (String) HsBase.getAnsiSqlMap().get("pre_sort.select");
			PageParam pageParam = new PageParam(null);
			pageParam.setPageSize(pageSize);
			PageQuery pageQuery = null;
			try {
				 pageQuery= new PageQuery(sql,new Object[]{parentId},pageParam,
						 new ReverseLoadData(){
					 public Object load(Map map) {
						SortBean sortBean = new SortBean();
						sortBean.setSortId(new Integer(((Long)map.get("sortId")).intValue()));
						sortBean.setParentSortId(new Integer(((Long)map.get("parentSortId")).intValue()));
						sortBean.setSortName(map.get("sortName").toString());
						return sortBean;
					}});
				 return pageQuery.getPageUtil().resultList;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return null;
		}
	
	
	/**
	 *  通过分类主键为parentId的分类的下级分类 进行分页
	 * @param parentId  父分类id
	 * @param pageParam 分页对象
	 * @return
	 */
	public PageUtil getSortSearchChi(Integer parentId,PageParam pageParam){
		String sql = (String) HsBase.getAnsiSqlMap().get("pre_sort.select");
		PageQuery pageQuery = null;
		try {
			 pageQuery= new PageQuery(sql,new Object[]{parentId},pageParam,
					 new ReverseLoadData(){
				 public Object load(Map map) {
					SortBean sortBean = new SortBean();
					sortBean.setSortId(new Integer(((Long)map.get("sortId")).intValue()));
					sortBean.setParentSortId(new Integer(((Long)map.get("parentSortId")).intValue()));
					sortBean.setSortName(map.get("sortName").toString());
					return sortBean;
				}});
			 return pageQuery.getPageUtil();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 批量添加分类
	 * @param data
	 * @return
	 * @throws Exception 
	 */
	public boolean impleSort(List data) throws Exception{
		if(data == null || data.isEmpty())
			return false;
		String sql = (String) HsBase.getAnsiSqlMap().get("pre_sort.insert");
		HsPreparedStatement st = new HsPreparedStatement(sql); 
		Iterator iterator = data.iterator();
		List par = new ArrayList();
		while(iterator.hasNext())
		{
			SortExcelBean sortExcelBean= (SortExcelBean)iterator.next();
			Object[] parArr = new Object[2];
			parArr[0] = sortExcelBean.getSortName();
			parArr[1] = sortExcelBean.getParentSortId();
			par.add(parArr);
		}
		String[] pid= st.executeUpdateReturnIdVolume(par);
		this.impleSortChild(data, pid);
//		p.setString(1, sortBean.getSortName());
//		p.setInt(2, sortBean.getParentSortId());
		return false;
	}
	
	/**
	 * 进行递归插入
	 * 一层一层插入数据
	 * @param data
	 * @param pid 父id
	 * @throws Exception
	 */
	private void impleSortChild(List data,String[] pid) throws Exception
	{
		Iterator iterator = data.iterator();
		int i = 0;
		while(iterator.hasNext())
		{
			List par = new ArrayList();
			SortExcelBean sortExcelBean= (SortExcelBean)iterator.next();
			List listBean = sortExcelBean.getChildList();
			if(listBean != null && !listBean.isEmpty())
			{
				Iterator iteratorTem = listBean.iterator();
				while(iteratorTem.hasNext())
				{
					SortExcelBean sortExcelBeanTem = (SortExcelBean)iteratorTem.next();
					sortExcelBeanTem.setParentSortId(new Integer(pid[i]));
				}
				impleSort(listBean);
			}
			
			i++;
		}
	}
	
	
	
	public String test(String sortId)
	{
//		where preid in(select preid from app.pre_sort_relation where sortid in("+sortId+"))
		System.out.println("***************************");
		String sql = "select * from app.pre_info ";
		try {
		HsPreparedStatement st = new HsPreparedStatement(sql);
		HsResultSet hsResultSet;
		
			hsResultSet = st.executeQuery();
		
		StringBuffer str = new StringBuffer();
		for(int i=0; i< 1000;i++)
		{
			if(hsResultSet.next())
			{
				String id =hsResultSet.getString("id");
				str.append(id+" ");
			}
			
		}
		System.out.println(":"+str);
		System.out.println("***************************");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "";
	}
}
